# 作业：
# 1、类作业，统计镰刀和斧头的数量，要使用类方法
import random


# class Tool:
#     sickle_count = 0
#     axe_count = 0
#     def __init__(self,tool_name):
#         if "斧头" in tool_name:
#             Tool.axe_count+=1
#         if "镰刀" in tool_name:
#             Tool.sickle_count += 1
#     @classmethod
#     def count_tool(self):
#         return f"镰刀的数量为：{self.sickle_count}个，斧头的数量为{self.axe_count}个"


# print(Tool.count_tool())
# Tool("斧头")
# Tool("镰刀A版本")
# print(Tool.count_tool())




# 2、做一个学生点名系统，系统里支持：存入学生、更改学生、删除学生、点到学生姓名方法
#
# 存入学生（姓名、年龄、家庭地址、手机号）： 存入成功后，提示xxx存入成功
# 更改学生（姓名、年龄、家庭地址、手机号）：可以修改学生内容的任意一个字段
# 删除学生：删除的学生将不在展示
# 展示全部学生：可以看到全部的学生姓名
# 学生姓名：随机抽取任意一个存在的学生信息，然后打印：到！。举例： 刘闯到！
#
# 以上内容，需要连接数据库，每次的数据都需要增、删、改、查去做
# import pymysql
# class StudentSystemManger:
#     def connect(self):
#         self.db = pymysql.connect(user = 'root',password = '123456',host="47.104.65.165",database="test",cursorclass=pymysql.cursors.DictCursor)
#         self.cursor = self.db.cursor()
#     def close(self):
#         self.cursor.close()
#         self.db.close()
#     def add_student_data(self,name,age,address,phone):
#         self.connect()
#         try:
#             self.cursor.execute("create table student(name varchar(30),age int,address varchar(30),phone varchar(30))")
#             self.db.commit()
#         except:
#             pass
#         self.cursor.execute(f"select * from student where name='{name}'")
#         result = self.cursor.fetchone()
#         if result:
#             self.close()
#             return 0
#         else:
#             self.cursor.execute(f"insert into student values('{name}',{age},'{address}','{phone}')")
#             self.db.commit()
#             self.close()
#             return 1
#     def update_student_data(self,old_name,new_name,age,address,phone):
#         self.connect()
#         self.cursor.execute(f"select * from student where name='{old_name}'")
#         result = self.cursor.fetchone()
#         if result:
#             self.cursor.execute(f"update student set name='{new_name}',age={age},address='{address}',phone='{phone}' where name='{old_name}'")
#             self.db.commit()
#             self.close()
#             return 1
#         else:
#             self.close()
#             return 0
#     def delete_student_data(self,name):
#         self.connect()
#         self.cursor.execute(f"select * from student where name='{name}'")
#         result = self.cursor.fetchone()
#         if result:
#             self.cursor.execute(f"delete from student where name = '{name}'")
#             self.db.commit()
#             self.close()
#             return 1
#         else:
#             self.close()
#             return 0
#     def show_all_student(self):
#         self.connect()
#         self.cursor.execute("select name from student")
#         result = self.cursor.fetchall()
#         self.close()
#         return result
#     def ramdon_student(self):
#         self.connect()
#         self.cursor.execute("select name from student")
#         result = self.cursor.fetchall()
#         random_name = random.choice(result)
#         self.close()
#         return random_name
#
# s1 = StudentSystemManger()
# s1.add_student_data("大天才",99,"沈阳","15987445631")
# s1.add_student_data("大天才1",99,"沈阳","15987445631")
# print(s1.show_all_student())
# print(s1.ramdon_student())
# s1.delete_student_data("大天才PLUS")





# 3、新建学生成绩管理系统，随机生成100个 学生信息插入到数据库：
# 学生信息组成：学号、学生姓名、英语成绩、数学成绩、语文成绩、班级号（班级号有：1809A，1809B，1810A，1810B）
#
# 成绩划分为：优秀，良好，中等，及格，不及格。良好比中等好。
#
# 优秀：90—100之间（包括90）
# 良好：80-90
# 中等：70-80
# 及格：60-70
# 不合格：分数低于60分。
#
# 成绩管理系统提供以下方法：
# 《1》 查询所有英语成绩为优秀的、良好的、中等、及格和不合格的学生姓名和班级号
# 《2》 查询1809A，1809B，1810A，1810B每个班级的数学成绩，并且是降序返回
# 《3》 查询1809A，1809B，1810A，1810B每个班级的总人数，并且由大到小排序返回
# 《4》 查询英语、数学、语文成绩不合格的学生姓名和对应的班级号，按照不合格的人数，由大到小排序
# from faker import Faker
# class ScoreManagementSystem:
#     def connect(self):
#         self.db = pymysql.connect(user='root', password='123456', host="47.104.65.165", database="test",
#                                   cursorclass=pymysql.cursors.DictCursor)
#         self.cursor = self.db.cursor()
#
#     def close(self):
#         self.cursor.close()
#         self.db.close()
#     def random_student_info(self):
#         self.connect()
#         try:
#             self.cursor.execute("create table student1(id int primary key auto_increment,name varchar(30),"
#                                 "english int(30),math int(30),chinese int(30),class varchar(30) )")
#             self.db.commit()
#         except:
#             pass
#         faker = Faker(locale="zh_cn")
#         for i in range(100):
#             name = faker.name()
#             score_math = random.randint(0,100)
#             score_Chinese = random.randint(0,100)
#             score_English = random.randint(0,100)
#             class1 = ["1809A","1809B","1810A","1810B"]
#             #print(f"insert into student1(name,english,math,chinese,class) values('{name}','{score_English}','{score_math}','{score_Chinese}','{random.choice(class1)}')")
#             self.cursor.execute(f"insert into student1(name,english,math,chinese,class) values('{name}',{score_English},{score_math},{score_Chinese},'{random.choice(class1)}')")
#             self.db.commit()
#         self.close()
#     # 查询所有英语成绩为优秀的、良好的、中等、及格和不合格的学生姓名和班级号
#     # 优秀：90—100之间（包括90）
#     # 良好：80-90
#     # 中等：70-80
#     # 及格：60-70
#     # 不合格：分数低于60分
#     def search_student_englishScore(self):
#         self.connect()
#         # 优秀：90—100之间（包括90）
#         self.cursor.execute("select name from student1 where english >= 90 and english <=100")
#         excellent = self.cursor.fetchall()
#
#         #  良好：80-90
#         self.cursor.execute("select name from student1 where english < 90 and english >= 80")
#         good = self.cursor.fetchall()
#
#         # 中等：70-80
#         self.cursor.execute("select name from student1 where english >=70 and english < 80")
#         medium = self.cursor.fetchall()
#
#         # 及格：60-70
#         self.cursor.execute("select name from student1 where english >=60 and english < 70")
#         pass1 = self.cursor.fetchall()
#
#         #不合格：分数低于60分
#         self.cursor.execute("select name from student1 where english <60")
#         unqualified = self.cursor.fetchall()
#
#         self.close()
#         return f"优秀：90—100之间（包括90）:{excellent} \n\n良好：80-90：{good} \n\n中等：70-80：{medium}  \n\n及格：60-70 ：{pass1} \n\n不合格：分数低于60分 ：{unqualified}\n\n"
#
#     #查询1809A，1809B，1810A，1810B每个班级的数学成绩，并且是降序返回
#     def Descending(self):
#         self.connect()
#         self.cursor.execute("select math,class from student1 where class='1809A' order by math desc")
#         result1 = self.cursor.fetchall()
#
#         self.cursor.execute("select math,class from student1 where class='1809B' order by math desc")
#         result2 = self.cursor.fetchall()
#
#         self.cursor.execute("select math,class from student1 where class='1810A' order by math desc")
#         result3 = self.cursor.fetchall()
#
#         self.cursor.execute("select math,class from student1 where class='1810B' order by math desc")
#         result4 = self.cursor.fetchall()
#         self.close()
#         return f"1809A :{result1} \n\n1809B：{result2} \n\n1810A：{result3}  \n\n1810B ：{result4}\n\n"
#
#     #《3》 查询1809A，1809B，1810A，1810B每个班级的总人数，并且由大到小排序返回
#     def class_count(self):
#         self.connect()
#         self.cursor.execute("select count(*) as '班级总人数',class from student1  group by class order by count(*) desc;")
#         result = self.cursor.fetchall()
#         self.close()
#         return result
#
#     # 《4》 查询英语、数学、语文成绩不合格的学生姓名和对应的班级号，按照不合格的人数，由大到小排序
#     def unqualified_sort(self):
#         self.connect()
#         self.cursor.execute("select name,class from student1 where class='1809A' and ( english < 60 and math <60 and chinese < 60)")
#         result1 = self.cursor.fetchall()
#
#         self.cursor.execute("select name,class from student1 where class='1809B' and ( english < 60 and math <60 and chinese < 60)")
#         result2 = self.cursor.fetchall()
#
#         self.cursor.execute("select name,class from student1 where class='1810A' and ( english < 60 and math <60 and chinese < 60)")
#         result3 = self.cursor.fetchall()
#
#         self.cursor.execute("select name,class from student1 where class='1810B' and ( english < 60 and math <60 and chinese < 60)")
#         result4 = self.cursor.fetchall()
#         dict1 = {"1809A":len(result1),"1809B":len(result2),"1810A":len(result3),"1810B":len(result4)}
#         dict_new = list(dict1.items())
#         for i in range(len(dict_new)):
#             for j in range(len(dict_new) -1 - i):
#                 if dict_new[j][1] < dict_new[j + 1][1]:
#                     dict_new[j], dict_new[j + 1] = dict_new[j + 1], dict_new[j]
#         return dict(dict_new)
#
#
#
# s1 = ScoreManagementSystem()
# print(s1.unqualified_sort())


# 4、定义一个图书管理系统类，图书可以新增、修改、删除，要求新增、修改、删除的图书，都要出现在数据库中
# 新增：图书名、作者名称、新建年月日
# 修改：图书名、作者名称、新建年月日
# 删除：图书删掉，且不在展示
# 查看：全量查询、精确查询、模糊查询需要用户传入形参来控制返回结果

#
# class BookManagerSystem:
#     def connect(self):
#         self.db = pymysql.connect(user='root', password='123456', host="47.104.65.165", database="test",
#                                   cursorclass=pymysql.cursors.DictCursor)
#         self.cursor = self.db.cursor()
#     def close(self):
#         self.cursor.close()
#         self.db.close()
#
#     def add_book(self,bookname,name,addtime):
#         self.connect()
#         try:
#             self.cursor.execute("create table book(id int primary key auto_increment,bookname varchar(30),name varchar(30),addtime varchar(30))")
#             self.db.commit()
#         except:
#             pass
#         self.cursor.execute(f"insert into book(bookname,name,addtime) values('{bookname}','{name}','{addtime}')")
#         self.db.commit()
#         self.close()
#         return 1
#     def update_book(self,bookold_name,booknew_name,name,addtime):
#         self.connect()
#         self.cursor.execute(f"update book set bookname='{booknew_name}',name='{name}',addtime='{addtime}' where bookname='{bookold_name}'")
#         self.db.commit()
#         self.close()
#         return 1
#
#     def delete_book(self,bookname):
#         self.connect()
#         self.cursor.execute(f"delete from book where bookname='{bookname}'")
#         self.db.commit()
#         self.close()
#         return 1
#
#     # 全量查询、精确查询、模糊查询
#     def search_bookname(self,type = None,bookname = None): # type : 1 全量   2 精确  3模糊
#         self.connect()
#         if type == 1 or type == "1":
#             self.cursor.execute(f"select bookname from book")
#             result = self.cursor.fetchall()
#             return result
#         elif type == 2 or type == "2":
#             if bookname:
#                 self.cursor.execute(f"select bookname from book where bookname = '{bookname}'")
#                 result = self.cursor.fetchall()
#                 return result
#             else:
#                 return 0
#         elif type == 3 or type == "3":
#             if bookname:
#                 self.cursor.execute(f"select bookname from book where bookname like '%{bookname}%'")
#                 result = self.cursor.fetchall()
#                 return result
#             else:
#                 return 0
#
#
# b1 = BookManagerSystem()
# print(b1.add_book("小天才", "范庆才", "2023-8-30"))
# print(b1.add_book("小天才1", "范庆才1", "2023-8-30"))
# print(b1.add_book("小天才2", "范庆才2", "2023-8-30"))
# print(b1.update_book("大天才", "小天才puls","范庆才", "2023-8-30"))
# print(b1.delete_book("小天才puls"))
# print(b1.search_bookname(1))
# print(b1.search_bookname(2,"小天才"))
# print(b1.search_bookname(3,"天才"))






